package com.sunwayworld.common.utils.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;

import com.sunwayworld.common.utils.BeanUtils;
import com.sunwayworld.common.utils.ConnManager;
import com.sunwayworld.common.utils.QueryUtils;
import com.sunwayworld.common.utils.SQLOracleUtil;
import com.sunwayworld.common.utils.SplitPage;
import com.sunwayworld.common.utils.VCommList;

/**
 * 
 * <p>
 * Title: 查询Session
 * </p>
 * 
 * <p>
 * Description: 只负责通用查询
 * </p>
 * 
 * <p>
 * Copyright: Copyright (c) 2005
 * </p>
 * 
 * <p>
 * Company: sunwayworld
 * </p>
 * 
 * @author lim
 * @version 1.0
 */
public class QueryOracle implements QueryUtils {

	public Object getObject(String sqlStr, Class className) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = ConnManager.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sqlStr);
			if (rs == null) {
				return null;
			}

			Object obj = null;
			if (rs.next()) {
				obj = className.newInstance();
				BeanUtils.copyProperties(rs, obj);

			}

			return obj;

		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			ConnManager.close(conn, stmt, rs);
			conn = null;
			stmt = null;
			rs = null;
		}
	}

	@SuppressWarnings("unchecked")
	public VCommList getVCommList(String sqlStr, Class className) {
		VCommList vlist = new VCommList();

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		int num = 0; // 总行数

		try {
			conn = ConnManager.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sqlStr);
			if (rs == null) {
				return vlist;
			}

			int row = 0;
			Object obj = null;
			while (rs.next()) {
				obj = className.newInstance();
				BeanUtils.copyProperties(rs, obj);
				row++;

				vlist.add(obj);
			}

			if (rs.last()) {
				num = rs.getRow();
			} else {
				num = 0;
			}
			vlist.setCurrPage(1);
			vlist.setNum(num);
			vlist.setNumPerPage(num);
			vlist.setPageNum(1);

			return vlist;

		} catch (Exception ex) {
			ex.printStackTrace();
			return vlist;
		} finally {
			ConnManager.close(conn, stmt, rs);
			conn = null;
			stmt = null;
			rs = null;
		}
	}

	public VCommList getVCommList(String sqlStr, Class className,
			SplitPage spage) {

		return getVCommList(sqlStr, null, className, spage);
	}

	@SuppressWarnings("unchecked")
	public VCommList getVCommList(String sqlStr, String[] sumcols,
			Class className, SplitPage spage) {

		VCommList vlist = new VCommList();

		HashMap<String, Double> sumInfo = new HashMap<String, Double>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			// 分页信息
			int numPerPage = spage.getNumPerPage();
			if (numPerPage <= 0)
				numPerPage = SplitPage.CONST_NUMPERPAGE;
			int currPage = spage.getCurrPage();
			if (currPage <= 1)
				currPage = SplitPage.CONST_CURRPAGE;
			int num = 0; // 总行数
			// 分页信息-end

			conn = ConnManager.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			if (sumcols != null) {

				rs = stmt.executeQuery(SQLOracleUtil.getSumsSqlStr(sqlStr,
						sumcols, true));
			} else {
				rs = stmt.executeQuery(SQLOracleUtil.getCountSqlStr(sqlStr,
						true));
			}

			if (rs.next()) {
				num = rs.getInt("CNT");
				if (sumcols != null) {
					for (int i = 0; i < sumcols.length; i++) {
						String tmp = "sum" + sumcols[i];
						try {
							sumInfo.put(tmp.toUpperCase(), new Double(rs
									.getDouble(i + 1)));
						} catch (Exception ex) {
							sumInfo.put(tmp.toUpperCase(), new Double(0));
						}
					}
					vlist.setSumInfo(sumInfo);
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ex) {

				}
			}

			fillCommList(vlist, spage, num); // 添充vlist 内的分页信息
			if (num > 0) { // 有记录

				rs = stmt.executeQuery(SQLOracleUtil.getCorrectSqlStr(sqlStr,
						currPage, numPerPage));

				while (rs.next()) {
					Object obj = className.newInstance();
					BeanUtils.copyProperties(rs, obj);
					vlist.add(obj);
				}
			}

			return vlist;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			ConnManager.close(conn, stmt, rs);
			conn = null;
			stmt = null;
			rs = null;
		}
	}

	public VCommList getVCommList(String sqlStr, String[] colsum,
			Class className) {
		return getVCommList(sqlStr, null, className);
	}

	/**
	 * 
	 * @param vlist
	 *            取回的list
	 * @param spage
	 * @param nCount
	 *            总条数
	 */
	private void fillCommList(VCommList vlist, SplitPage spage, int nCount) {

		if (vlist == null) {
			return;
		}
		if (spage == null) {
			spage=new SplitPage();
			spage.setNumPerPage(SplitPage.CONST_NUMPERPAGE);
			spage.setCurrPage(SplitPage.CONST_CURRPAGE);
		}
		vlist.setNumPerPage(spage.getNumPerPage());
		vlist.setCurrPage(spage.getCurrPage());
		vlist.setNum(nCount);
		vlist.setPageNum(nCount / vlist.getNumPerPage());
		if ((vlist.getPageNum() * vlist.getNumPerPage()) < nCount)
			vlist.setPageNum(vlist.getPageNum() + 1);
	}
}
